﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExcelAddIn
{
    public partial class GZBoHZ : Form
    {
        Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
        public GZBoHZ()
        {
            InitializeComponent();
        }

        private void buttonSum_Click(object sender, EventArgs e)
        {
            var selectRang = app.InputBox("要汇总的区域", "请选择需要汇总的单元格，按住Ctrl键可多选", Type: 8);
            if (selectRang is Range)
            {
                InputeSum.Text = ((Range)selectRang).Address;
            }
        }

        private void buttonAvg_Click(object sender, EventArgs e)
        {
            var selectRang = app.InputBox("要汇总的区域", "请选择需要求平均值的单元格，按住Ctrl键可多选", Type: 8);
            if (selectRang is Range)
            {
                InputeAvg.Text = ((Range)selectRang).Address;
            }
        }

        private void buttonOk_Click(object sender, EventArgs e)
        {
            DialogResult dgResult = openFileDialog1.ShowDialog();
            if(dgResult==DialogResult.OK)
            {
                if (!string.IsNullOrEmpty(InputeAvg.Text))
                    AvgAllSheet(InputeAvg.Text);
                if (!string.IsNullOrEmpty(InputeSum.Text))
                    SumAllSheet(InputeSum.Text);
            }
        }

        private void SumAllSheet(string allRange)
        {
            double allSum = 0;
            //获取所有集合
            Dictionary<string, List<double?>> valueList = GetAllSheetCell(allRange);
            //获取当前选中区域
            Range range = ((Worksheet)(app.ActiveSheet)).get_Range(allRange);
            //循环所有选中区域
            int i = 0;
            foreach (Range cell in range.Cells)
            {
                allSum = 0;
                string rangText = cell.Address;//cell地址
                foreach (var sheetsDic in valueList)
                {
                    allSum += sheetsDic.Value[i] ?? 0;
                }
                ((Worksheet)app.ActiveSheet).Range[rangText].Value = allSum;
                i++;
            }
        }
        private void AvgAllSheet(string allRange)
        {
            double allSum = 0;
            //获取所有集合
            Dictionary<string, List<double?>> valueList = GetAllSheetCell(allRange);
            //获取当前选中区域
            Range range = ((Worksheet)(app.ActiveSheet)).get_Range(allRange);
            //循环所有选中区域
            int i = 0;
            int count=0;
            foreach (Range cell in range.Cells)
            {
                allSum = 0;
                count = 0;
                string rangText = cell.Address;//cell地址
                foreach (var sheetsDic in valueList)
                {
                    allSum += sheetsDic.Value[i] ?? 0;
                    if (sheetsDic.Value[i].HasValue)
                        count++;
                }
                if (count!=0)
                    cell.Value = allSum/count;
                i++;
            }
        }

        private Dictionary<string, List<double?>> GetAllSheetCell(string allRange)
        {
            double cellValue = 0;
            int sheetIndex = comboBox.SelectedIndex+1;
            List<double?> cellList;//sheets里面cell.value的集合
            Dictionary<string, List<double?>> resDic = new Dictionary<string, List<double?>>();

            Microsoft.Office.Interop.Excel.Application newApp = new Microsoft.Office.Interop.Excel.Application();
            newApp.ScreenUpdating = false;//不显示
            //循环所有文件
            foreach (var fileName in openFileDialog1.FileNames)
            {
                newApp.Workbooks.Open(@fileName);//打开文件
                if (newApp.Sheets.Count < sheetIndex)
                    continue;
                cellList = new List<double?>();
                //获取所有选中区域
                Range range = ((Worksheet)(newApp.Sheets[sheetIndex])).Range[allRange];
                //循环所有选中区域
                foreach (Range cell in range.Cells)
                {
                    if (cell.Value is Double)
                        cellList.Add(cell.Value);
                    else if (cell.Value is String && !string.IsNullOrEmpty(cell.Value))
                    {
                        Double.TryParse(cell.Value, out cellValue);
                        //说明不是数字
                        if (cellValue == 0 && !string.IsNullOrEmpty(cell.Value) && cell.Value != "0")
                            cellList.Add(null);
                        else if (cellValue != 0)//是数字
                            cellList.Add(cellValue);
                    }else
                        cellList.Add(null);
                }
                resDic.Add(Path.GetFileName(fileName)+ newApp.ActiveSheet.Name, cellList);
                newApp.Workbooks.Close();
            }
            return resDic;
        }

        private void GZBoHZ_Load(object sender, EventArgs e)
        {
            comboBox.SelectedItem = 1;
        }
    }
}
